/*
 * PartGroupDao.java
 *
 */
package com.sun.demo.addressbook.db;

import com.sun.demo.addressbook.Group;
import com.sun.demo.addressbook.PartGroup;
import com.sun.demo.addressbook.GroupListEntry;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 *
 */
public class PartGroupDao {
    private LppDbSetup lppDbSetup;
    private Connection dbConnection;
    private Properties dbProperties;
    private boolean isConnected;
    private String dbName;
    private PreparedStatement stmtCreatePartGroup;
    private PreparedStatement stmtGetGroupListEntries;
    private PreparedStatement stmtGetAssignedGroupEntries;
    private PreparedStatement stmtGetAvailableGroupEntries;
    private PreparedStatement stmtGetPartGroup;
    private PreparedStatement stmtDeletePartGroup;
    
    public PartGroupDao() {
        lppDbSetup = LppDbSetup.getLppDbSetup();
        System.out.println(lppDbSetup.getDatabaseDiskLocation());
        System.out.println(lppDbSetup.getDatabaseUrl());
    }
    
    public boolean connect() {
        String dbUrl = lppDbSetup.getDatabaseUrl();
        try {
            dbConnection = DriverManager.getConnection(dbUrl, dbProperties);
            stmtCreatePartGroup = dbConnection.prepareStatement(strCreatePartGroup, Statement.RETURN_GENERATED_KEYS);
            stmtGetPartGroup = dbConnection.prepareStatement(strGetPartGroup);
            stmtGetGroupListEntries = dbConnection.prepareStatement(strGetGroupListEntries);
            stmtGetAssignedGroupEntries = dbConnection.prepareStatement(strGetAssignedGroupEntries);
            stmtGetAvailableGroupEntries = dbConnection.prepareStatement(strGetAvailableGroupEntries);
            stmtDeletePartGroup = dbConnection.prepareStatement(strDeletePartGroup);
            
            isConnected = dbConnection != null;
        } catch (SQLException ex) {
            isConnected = false;
            ex.printStackTrace();
        }
        return isConnected;
    }
    
    public void disconnect() {
        if(isConnected) {
            String dbUrl = lppDbSetup.getDatabaseUrl();
            dbProperties.put("shutdown", "true");
            try {
                DriverManager.getConnection(dbUrl, dbProperties);
            } catch (SQLException ex) {
            }
            isConnected = false;
        }
    }
    
    public boolean createPartGroup(PartGroup record) {
        boolean success = false;
        try {
            stmtCreatePartGroup.clearParameters();
            
            stmtCreatePartGroup.setInt(1, record.getPartId());
            stmtCreatePartGroup.setInt(2, record.getGroupId());
            int rowCount = stmtCreatePartGroup.executeUpdate();
            success = true;
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        return success;
    }
    
    public boolean deletePartGroup(int partId, int groupId) {
        boolean bDeleted = false;
        try {
            stmtDeletePartGroup.clearParameters();
            stmtDeletePartGroup.setInt(1, partId);
            stmtDeletePartGroup.setInt(2, groupId);
            stmtDeletePartGroup.executeUpdate();
            bDeleted = true;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return bDeleted;
    }
    
    public boolean deletePartGroup(PartGroup record) {
        return deletePartGroup(record.getPartId(), record.getGroupId());
    }
    
    public List<GroupListEntry> getListEntries() {
        List<GroupListEntry> listEntries = new ArrayList<GroupListEntry>();
        try {
            ResultSet results = stmtGetGroupListEntries.executeQuery(strGetGroupListEntries);
            while(results.next()) {
                int id = results.getInt(1);
                String groupName = results.getString(2);
                GroupListEntry entry = new GroupListEntry(groupName, id);
                listEntries.add(entry);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listEntries;
    }
    
    public List<GroupListEntry> getAssignedGroupEntries(int partId) {
        List<GroupListEntry> listEntries = new ArrayList<GroupListEntry>();
        try {
            stmtGetAssignedGroupEntries.clearParameters();
            stmtGetAssignedGroupEntries.setInt(1, partId);
            ResultSet results = stmtGetAssignedGroupEntries.executeQuery();
            while(results.next()) {
                int groupId = results.getInt(1);
                String groupName = results.getString(2);
                GroupListEntry entry = new GroupListEntry(groupName, groupId);
                listEntries.add(entry);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listEntries;
    }
    
    public List<GroupListEntry> getAvailableGroupEntries(int partId) {
        List<GroupListEntry> listEntries = new ArrayList<GroupListEntry>();
        try {
            stmtGetAvailableGroupEntries.clearParameters();
            stmtGetAvailableGroupEntries.setInt(1, partId);
            ResultSet results = stmtGetAvailableGroupEntries.executeQuery();
            while(results.next()) {
                int groupId = results.getInt(1);
                String groupName = results.getString(2);
                GroupListEntry entry = new GroupListEntry(groupName, groupId);
                listEntries.add(entry);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return listEntries;
    }
    
    public PartGroup getPartGroup(int partId, int groupId) {
        PartGroup partGroup = null;
        try {
            stmtGetPartGroup.clearParameters();
            stmtGetPartGroup.setInt(1, partId);
            stmtGetPartGroup.setInt(2, groupId);
            ResultSet result = stmtGetPartGroup.executeQuery();
            if (result.next()) {
                int resultPartId = result.getInt("PARTID");
                int resultGroupId = result.getInt("GROUPID");
                int id = result.getInt("ID");
                partGroup = new PartGroup(resultPartId, resultGroupId);
            }
        } catch(SQLException sqle) {
            sqle.printStackTrace();
        }
        
        return partGroup;
    }
    
    private static final String strGetPartGroup =
            "SELECT * FROM APP.PARTGROUP " +
            "WHERE PARTID = ? AND GROUPID = ?";
    
    private static final String strCreatePartGroup =
            "INSERT INTO APP.PARTGROUP " +
            "   (PARTID, GROUPID) " +
            "VALUES (?, ?)";
    
    private static final String strGetGroupListEntries =
            "SELECT PARTGROUP.GROUPID, GROUPDATA.GROUPNAME " +
            "FROM APP.PARTGROUP PARTGROUP, APP.GROUPDATA GROUPDATA "  +
            "ORDER BY GROUPDATA.GROUPNAME ASC";

    private static final String strGetAssignedGroupEntries =
            "SELECT PARTGROUP.GROUPID, GROUPDATA.GROUPNAME " +
            "FROM APP.PARTGROUP PARTGROUP " +
            "INNER JOIN APP.GROUPDATA ON PARTGROUP.GROUPID = APP.GROUPDATA.ID " +
            "WHERE PARTGROUP.PARTID = ? " +
            "ORDER BY GROUPDATA.GROUPNAME ASC";

    private static final String strGetAvailableGroupEntries =
            "SELECT GROUPDATA.ID, GROUPDATA.GROUPNAME " +
            "FROM APP.GROUPDATA GROUPDATA "  +
            "WHERE GROUPDATA.GROUPNAME NOT IN "  +
            "(SELECT GROUPDATA.GROUPNAME " +
            "FROM APP.GROUPDATA GROUPDATA "  +
            "INNER JOIN APP.PARTGROUP ON APP.GROUPDATA.ID = PARTGROUP.GROUPID " +
            "WHERE (PARTGROUP.PARTID = ?)) "  +
            "ORDER BY GROUPDATA.GROUPNAME ASC";

    private static final String strDeletePartGroup =
            "DELETE FROM APP.PARTGROUP " +
            "WHERE PARTID = ? AND GROUPID = ?";
    
    public static void main(String[] args) {
        GroupDao db = new GroupDao();
        db.connect();
        db.disconnect();
    }
}
